/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package daos;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import models.*;
import util.WebConst;

/**
 *
 * @author trietto
 */
public class EditorialCategoryDAO {
    
    private PreparedStatement pStmtSelectCount = null;
    private PreparedStatement pStmtSelectAll = null;
    private PreparedStatement pStmtSelectAllPerPage = null;
    private PreparedStatement pStmtSelect = null;
    private PreparedStatement pStmtInsert = null;
    private PreparedStatement pStmtDelete = null;
    private PreparedStatement pStmtUpdate = null;
    private static EditorialCategoryDAO editorialCategoryDAO = null;

    public static EditorialCategoryDAO singleton() {
        if (editorialCategoryDAO == null) {
            editorialCategoryDAO = new EditorialCategoryDAO();
        }
        return editorialCategoryDAO;
    }

    public EditorialCategoryDAO() {
        try {
            pStmtSelectCount = DBConnection.getConnection().prepareStatement("SELECT Count(EditorialCatId) FROM EditorialCategories");
            pStmtSelectAllPerPage = DBConnection.getConnection().prepareStatement("SELECT * FROM EditorialCategories LIMIT ?,?");
            pStmtSelectAll = DBConnection.getConnection().prepareStatement("SELECT * FROM EditorialCategories");
            pStmtSelect = DBConnection.getConnection().prepareStatement("SELECT * FROM EditorialCategories WHERE EditorialCatId = ?");
            pStmtInsert = DBConnection.getConnection().prepareStatement("INSERT INTO EditorialCategories(Name) VALUES(?)");
            pStmtDelete = DBConnection.getConnection().prepareStatement("DELETE FROM EditorialCategories WHERE EditorialCatId = ?");
            pStmtUpdate = DBConnection.getConnection().prepareStatement("UPDATE EditorialCategories SET Name = ? WHERE EditorialCatId = ?");
        } catch (SQLException ex) {
            Logger.getLogger(EditorialCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

     public int recordCount(){
        int totalRecord = 0;
        try {
            ResultSet rs = pStmtSelectCount.executeQuery();
            while (rs.next()) {
                totalRecord = rs.getInt(1);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(CardTypeDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return totalRecord;
    }

     public ArrayList<EditorialCategory> selectAll() {
        ArrayList<EditorialCategory> lstEditorialCategories = new ArrayList<EditorialCategory>();
        try {
            ResultSet rs = pStmtSelectAll.executeQuery();
            while (rs.next()) {
                lstEditorialCategories.add(new EditorialCategory(rs.getInt(1), rs.getString(2)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(EditorialCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstEditorialCategories;
    }


    public ArrayList<EditorialCategory> selectAll(int page) {
        ArrayList<EditorialCategory> lstEditorialCategories = new ArrayList<EditorialCategory>();
        int offset = (page-1) * WebConst.ROW_PER_PAGE;
        try {
            pStmtSelectAllPerPage.setInt(1, offset);
            pStmtSelectAllPerPage.setInt(2, WebConst.ROW_PER_PAGE);
            ResultSet rs = pStmtSelectAllPerPage.executeQuery();
            while (rs.next()) {
                lstEditorialCategories.add(new EditorialCategory(rs.getInt(1), rs.getString(2)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(EditorialCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstEditorialCategories;
    }

    public EditorialCategory select(int editorialCatId) {
        EditorialCategory editorialCat = null;
        try {
            pStmtSelect.setInt(1, editorialCatId);
            ResultSet rs = pStmtSelect.executeQuery();
            while (rs.next()) {
                editorialCat = new EditorialCategory(rs.getInt(1), rs.getString(2));
            }
        } catch (SQLException ex) {
            Logger.getLogger(EditorialCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return editorialCat;
    }

    public String insert(EditorialCategory editorialCat) {
        String result = "";
        boolean bRet = false;
        try {            
            pStmtInsert.setString(1, editorialCat.getName());
            pStmtInsert.execute();
            bRet = true;
        } catch (SQLException ex) {            
            Logger.getLogger(EditorialCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = bRet ? "Successed" : "Failed";
        return result;
    }

    public String delete(int editorialCatId) {
        String result = "";
        int numOfRows = 0;
        try {
            pStmtDelete.setInt(1, editorialCatId);
            numOfRows = pStmtDelete.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(EditorialCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = numOfRows > 0 ? "Successed" : "Failed";

        return result;
    }

    public String update(EditorialCategory editorialCat) {
        String result = "";
        int numOfRows = 0;
        try {
            pStmtUpdate.setString(1, editorialCat.getName());
            pStmtUpdate.setInt(2, editorialCat.getEditorialCatId());
            numOfRows = pStmtUpdate.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(EditorialCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = numOfRows > 0 ? "Successed" : "Failed";

        return result;
    }
}
